"... on delete cascade ..."

[Val]

Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.

[deepak]

SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.

------------
Val at 12/8/99 5:36:38 PM

Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.

Thanks for that.
So, I guess I have to remove FKs and use triggers to look after data integrity?


------------
deepak at 12/8/99 6:18:44 PM

SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.

------------
Val at 12/8/99 5:36:38 PM

Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.

[deepak]

I don't understand why you need to remove foreign key constraints to delete the child records. You can delete them write away.

Deepak.

------------
at 12/8/99 6:24:53 PM

Thanks for that.
So, I guess I have to remove FKs and use triggers to look after data integrity?


------------
deepak at 12/8/99 6:18:44 PM

SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.

------------
Val at 12/8/99 5:36:38 PM

Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.

[Val]

Because I'm getting this error:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_2_1'. The conflict occurred in database 'Northwind',
table 'VAL_2', column 'ID_LINK'. The statement has been terminated.

I have VAL_1 table (id_1 is PK) and VAL_2 (id_2 is PK and ID_LINK is FK ref. VAL_1(id_1).
I'm trying to remove one record from VAL_1, which has children in VAL_2. I have a trigger on
'delete' for VAL_1, which looks like this:

CREATE TRIGGER [trg_del_val_1] ON [val_1]
FOR DELETE
AS
BEGIN
DELETE val_2 FROM val_2 INNER JOIN deleted
on val_2.id_link = deleted.id_1
END


If I run the delete statement I'm getting that error. If I'm disabling the FK_2_1 constraint,
everything is fine - the parent record from VAL_1 disappears, so do the children from VAL_2.

Any ideas?

Thank you very much.


------------
deepak at 12/8/99 6:34:33 PM


I don't understand why you need to remove foreign key constraints to delete the child records. You can delete them write away.

Deepak.

------------
at 12/8/99 6:24:53 PM

Thanks for that.
So, I guess I have to remove FKs and use triggers to look after data integrity?


------------
deepak at 12/8/99 6:18:44 PM

SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.

------------
Val at 12/8/99 5:36:38 PM

Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.

[Ray Miao]

You should delete from child first. SQL checks fkey constraint before firing trigger.


------------
Val at 12/8/99 6:53:56 PM

Because I'm getting this error:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_2_1'. The conflict occurred in database 'Northwind',
table 'VAL_2', column 'ID_LINK'. The statement has been terminated.

I have VAL_1 table (id_1 is PK) and VAL_2 (id_2 is PK and ID_LINK is FK ref. VAL_1(id_1).
I'm trying to remove one record from VAL_1, which has children in VAL_2. I have a trigger on
'delete' for VAL_1, which looks like this:

CREATE TRIGGER [trg_del_val_1] ON [val_1]
FOR DELETE
AS
BEGIN
DELETE val_2 FROM val_2 INNER JOIN deleted
on val_2.id_link = deleted.id_1
END


If I run the delete statement I'm getting that error. If I'm disabling the FK_2_1 constraint,
everything is fine - the parent record from VAL_1 disappears, so do the children from VAL_2.

Any ideas?

Thank you very much.


------------
deepak at 12/8/99 6:34:33 PM


I don't understand why you need to remove foreign key constraints to delete the child records. You can delete them write away.

Deepak.

------------
at 12/8/99 6:24:53 PM

Thanks for that.
So, I guess I have to remove FKs and use triggers to look after data integrity?


------------
deepak at 12/8/99 6:18:44 PM

SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.

------------
Val at 12/8/99 5:36:38 PM

Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.